﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using PPI.Entities.Inspections;
using PPI.Mobile.Logic;
using VMind.Global;

namespace PPI.Client.Logic.Inspection
{
    public class LNote
    {
        public static List<ENote> SelectAllIdName()
        {
            List<ENote> listNotes = new List<ENote>();

            DataTable dt = WebConsumer.Inspection.WInspectionNote.SelectAllIdName().Tables[0];

            foreach (DataRow dr in dt.Rows)
            {
                ENote resultType = new ENote(ConvertHelper.ToInt32(dr["Vendor_InspVisit_XmissionStructures_NoteId"]), dr["Name"].ToString());
                listNotes.Add(resultType);

            }
            return listNotes;
        }

        #region Mobile

        /// <summary>
        /// Query Mobile Database for All Inspection Note Records 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        /// <history>
        ///     WB 1/31/2009 - New Method added. Modified ENote to store ResultId
        /// </history>
        public static List<ENote> GetMobileAll(string connectionString)
        {
            #region Query
            StringBuilder _sql = new StringBuilder();
            _sql.AppendLine("Select ");
            _sql.AppendLine("   UtilityInspectionResultFk, ");
            _sql.AppendLine("   I.NoteFk, ");
            _sql.AppendLine("   N.Name as Note_Name ");
            _sql.AppendLine("FROM tb_UtilityInspectionResultsXNotes I ");
            _sql.AppendLine("   INNER JOIN tb_Notes N ON I.NoteFk = N.NoteId ");
            #endregion

            DataTable _dt = LMobileDatabase.ExecuteQueryDataset(_sql.ToString(), connectionString).Tables[0];

            List<ENote> _NoteList = new List<ENote>();
            foreach (DataRow dr in _dt.Rows)
            {
                ENote _note = new ENote(ConvertHelper.ToInt32(dr["NoteFk"]), ConvertHelper.Tostring(dr["Note_Name"]));
                _note.ResultId = dr.Field<int?>("UtilityInspectionResultFk");
                _NoteList.Add(_note);
            }
            return _NoteList;
        }

        public static List<ENote> GetMobileByResultId(string connectionString, Int32 resultId)
        {

            #region Query
            StringBuilder _sql = new StringBuilder();
            _sql.AppendLine("Select ");
            _sql.AppendLine("   I.NoteFk, ");
            _sql.AppendLine("   N.Name as Note_Name ");
            _sql.AppendLine("FROM tb_UtilityInspectionResultsXNotes I ");
            _sql.AppendLine("   INNER JOIN tb_Notes N ON I.NoteFk = N.NoteId ");
            _sql.AppendLine("Where UtilityInspectionResultFk = " + resultId + "");
            #endregion

            DataTable _dt = LMobileDatabase.ExecuteQueryDataset(_sql.ToString(), connectionString).Tables[0];

            List<ENote> _NoteList = new List<ENote>();
            foreach (DataRow dr in _dt.Rows)
            {
                ENote _note = new ENote(ConvertHelper.ToInt32(dr["NoteFk"]), ConvertHelper.Tostring(dr["Note_Name"]));
                _NoteList.Add(_note);
            }
            return _NoteList;
        }

        #region CRUD

        public static void DeleteMobile(EInspectionResult result, string connectionString)
        {
            StringBuilder _Query = new StringBuilder();
            
            List<SqlParameter> listParameters = new List<SqlParameter>();
            listParameters.Add(DBHelper.CreateIntParameter("@UtilityInspectionResultFk",ParameterDirection.Input,false,result.Id));

            #region Query

            _Query.AppendLine("Delete tb_UtilityInspectionResultsXNotes where UtilityInspectionResultFk=@UtilityInspectionResultFk");


            #endregion

            LMobileDatabase.ExecuteSingleQueryWhithParameters(_Query.ToString(),connectionString,listParameters.ToArray());





        }

        public static void InsertMobile(ENote note,Int32 resultId, string connectionString)
        {
            StringBuilder _Query = new StringBuilder();

            List<SqlParameter> listParameters = new List<SqlParameter>();
            listParameters.Add(DBHelper.CreateIntParameter("@UtilityInspectionResultFk", ParameterDirection.Input, false, resultId));
            listParameters.Add(DBHelper.CreateIntParameter("@NoteFk", ParameterDirection.Input, false, note.Id));

            #region Query

            _Query.AppendLine("Insert into tb_UtilityInspectionResultsXNotes(UtilityInspectionResultFk,NoteFk)values (@UtilityInspectionResultFk,@NoteFk)");


            #endregion

            LMobileDatabase.ExecuteSingleQueryWhithParameters(_Query.ToString(), connectionString, listParameters.ToArray());





        }

        #endregion

        #endregion

        public static void DeleteByInspectionResult(EInspectionResult result)
        {
            WebConsumer.Inspection.WInspectionNote.DeleteByInspectionResult(result.Id);
        }

        public static Int32 Save(ENote note,EInspectionResult result)
        {
            return WebConsumer.Inspection.WInspectionNote.Insert(note.Id,result.Id);
        }


        private static List<ENote> ParseDatatableNote(DataTable _dt)
        {
            List<ENote> _NoteList = new List<ENote>();
            foreach (DataRow dr in _dt.Rows)
            {
                ENote _note = new ENote(ConvertHelper.ToInt32(dr["Vendor_InspVisit_XmissionStructures_NoteFk"]), ConvertHelper.Tostring(dr["Note_Name"]));
                _NoteList.Add(_note);
            }
            return _NoteList;
        }
        public static List<ENote> GetByResultId(int id)
        {
            DataTable notesDatatable = WebConsumer.Inspection.WInspectionNote.GetNoteByInspectionResult(id).Tables[0];
            return ParseDatatableNote(notesDatatable);
        }
    }
}
